if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Invoice_PUR]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Invoice_PUR]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Invoice_PURitem]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Invoice_PURitem]
GO

CREATE TABLE [dbo].[Invoice_PUR] (
	[Inv_PURID] [bigint] IDENTITY (1, 1) NOT NULL ,
	[Inv_PURDate] [datetime] NOT NULL ,
	[Inv_PURVNo] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[SessionID] [smallint] NOT NULL ,
	[Remarks] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ReadOnly] [bit] NOT NULL ,
	[LoginID] [int] NOT NULL ,
	[HostName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[EntryDateTime] [datetime] NULL ,
	[ModifyID] [int] NULL ,
	[ModifyHostName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ModifyDateTime] [datetime] NULL ,
	[VehNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[SumQTY] [numeric](15, 4) NOT NULL ,
	[SUMGrossWT] [numeric](15, 4) NOT NULL ,
	[SUMBasketWT] [numeric](15, 4) NOT NULL ,
	[SUMNetWT] [numeric](15, 4) NOT NULL ,
	[SUMAmount] [numeric](15, 4) NOT NULL ,
	[SUMDlrCom] [numeric](15, 4) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Invoice_PURitem] (
	[Inv_PURitemID] [bigint] IDENTITY (1, 1) NOT NULL ,
	[Inv_PURID] [bigint] NOT NULL ,
	[PartyID] [int] NULL ,
	[VarietyID] [smallint] NULL ,
	[Qty] [numeric](15, 4) NOT NULL ,
	[GrossWT] [numeric](15, 4) NOT NULL ,
	[BasketWT] [numeric](15, 4) NOT NULL ,
	[NetWT] [numeric](15, 4) NOT NULL ,
	[Rate] [numeric](15, 4) NOT NULL ,
	[Amount] [numeric](15, 4) NOT NULL ,
	[DlrID] [int] NULL ,
	[DlrCom] [numeric](15, 4) NOT NULL ,
	[Type] [tinyint] NOT NULL ,
	[DRemarks] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Invoice_PUR] WITH NOCHECK ADD 
	CONSTRAINT [PK_Invoice_PUR] PRIMARY KEY  CLUSTERED 
	(
		[Inv_PURID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Invoice_PURitem] WITH NOCHECK ADD 
	CONSTRAINT [PK_Invoice_PURitem] PRIMARY KEY  CLUSTERED 
	(
		[Inv_PURitemID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Invoice_PUR] ADD 
	CONSTRAINT [DF_Invoice_PUR_ReadOnly] DEFAULT (0) FOR [ReadOnly],
	CONSTRAINT [DF_Invoice_PUR_LoginID] DEFAULT (1) FOR [LoginID],
	CONSTRAINT [DF_Invoice_PUR_HostName] DEFAULT (host_name()) FOR [HostName],
	CONSTRAINT [DF_Invoice_PUR_EntryDateTime] DEFAULT (getdate()) FOR [EntryDateTime],
	CONSTRAINT [DF_Invoice_PUR_SumQTY] DEFAULT (0) FOR [SumQTY],
	CONSTRAINT [DF_Invoice_PUR_GrossWT] DEFAULT (0) FOR [SUMGrossWT],
	CONSTRAINT [DF_Invoice_PUR_SUMBasketWT] DEFAULT (0) FOR [SUMBasketWT],
	CONSTRAINT [DF_Invoice_PUR_SUMNetWT] DEFAULT (0) FOR [SUMNetWT],
	CONSTRAINT [DF_Invoice_PUR_SUMAmount] DEFAULT (0) FOR [SUMAmount],
	CONSTRAINT [DF_Invoice_PUR_SUMDlrCom] DEFAULT (0) FOR [SUMDlrCom]
GO

ALTER TABLE [dbo].[Invoice_PURitem] ADD 
	CONSTRAINT [DF_Invoice_PURitem_Qty] DEFAULT (0) FOR [Qty],
	CONSTRAINT [DF_Invoice_PURitem_GrossWT] DEFAULT (0) FOR [GrossWT],
	CONSTRAINT [DF_Invoice_PURitem_BasketWT] DEFAULT (0) FOR [BasketWT],
	CONSTRAINT [DF_Invoice_PURitem_NetWT] DEFAULT (0) FOR [NetWT],
	CONSTRAINT [DF_Invoice_PURitem_Rate] DEFAULT (0) FOR [Rate],
	CONSTRAINT [DF_Invoice_PURitem_Amount] DEFAULT (0) FOR [Amount],
	CONSTRAINT [DF_Invoice_PURitem_DlrCom] DEFAULT (0) FOR [DlrCom],
	CONSTRAINT [DF_Invoice_PURitem_Type] DEFAULT (0) FOR [Type]
GO